BigQueryのテーブル作成実践(ローカルファイルアップロード)
Google BigQueryでは、テーブル作成を行う術が複数提供されています。当エントリではその中から「ローカルファイルアップロード」による作成方法について、実践を交えて見ていきたいと思います。
目次
コンソール経由で実践
この機能で利用出来るローカルファイルの容量は10MBまで。それ以上のサイズとなる場合は『Google Cloud Storage』にアップロードしてそっち経由で実践してね、とガイドされています。
「参照」ボタン押下で対象ファイルを指定。今回はMovieLensのデータを使いました。
ファイルアップロードでは以下5種のファイル種別に対応しています。
- CSV
- JSONL(改行区切りJSON)
- Avro
- Parquet
- ORC
スキーマ定義については「スキーマとパラメータの自動検出」を利用する事が出来ます(使わない場合はCREATE TABLEの時の様に個別にカラム定義を行う形となります)。ここではこの自動検出機能を使ってみます。
詳細オプションについても、以下のような形で細かな指定が可能です。一旦ここはそのまま設定せずに進めてみます。[テーブルを作成]を選択。
程なくしてテーブルが作成されました。スキーマ定義は以下のような内容となりました。
テーブル詳細と、
テーブル内容のプレビューはこの様な形に。
ここまでの手順で、「ローカルファイルからのテーブル作成」では以下の入力パラメータを取り得る事が確認出来ました。
- ローカルファイルの参照パス
- ファイル形式
- データセット名及びテーブル名
- スキーマと入力パラメータの「自動検知」設定
- カラムスキーマ設定
- パーティション設定
- クラスタ設定
- 書き込み設定
- 空の場合に書き込む
- テーブルに追加する
- テーブルを上書きする
- 許可されているエラー数
- 不明な値を無視するか否か
- フィールドの区切り文字指定
- スキップするヘッダー行(数)の指定
- 引用符で囲まれた改行を許可するか否か(offの場合、改行文字はそのまま改行扱いとなる)
- ジャグ行を許可するか否か(末尾のオプション列が欠落している行における対応)
CLIで実践(bq load)
CLIでローカルファイルアップロードによるテーブル作成を行う場合はbq loadコマンドを用います。
上記コンソールの取り込みをAmazon Linux2上に格納した対象ファイルでbq loadコマンドにより実践した際のコマンドが以下。自動検知は--autodetect、ファイル種別は--source_formatでそれぞれ指定しています。
$ bq load --autodetect \ --location=asia-northeast1 \ --source_format=CSV \ cmbqdataset.bqtable_from_localfile_autodetect \ /home/ec2-user/ml-25m/movies.csv Upload complete. Waiting on bqjob_rxxxxxxxxxxxxxxxxxxxxxx_1 ... (0s) Current status: DONE
自動検知せずに、スキーマ名を各々指定した場合はどうなるか。試しに、--autodetectを外し、末尾にスキーマ指定を行って実行してみます。すると以下の様にエラーが発生。
$ bq load \ --location=asia-northeast1 \ --source_format=CSV \ cmbqdataset.bqtable_from_localfile_def_schema \ /home/ec2-user/ml-25m/movies.csv \ movieId:INTEGER,title:STRING,genres:STRING Upload complete. Waiting on bqjob_rxxxxxxxxxxxxxxxxxxxxxx_1 ... (0s) Current status: DONE BigQuery error in load operation: Error processing job 'cm-xxxxxxxxxxx:bqjob_rxxxxxxxxxxxxxxxxxxxxx_1': Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details. Failure details: - Error while reading data, error message: Could not parse 'movieId' as int for field movieId (position 0) starting at location 0
メッセージを元に取り込み対象のファイルを確認してみるとこのファイル、ヘッダ行が含まれていました。
movieId,title,genres 1,Toy Story (1995),Adventure|Animation|Children|Comedy|Fantasy 2,Jumanji (1995),Adventure|Children|Fantasy 3,Grumpier Old Men (1995),Comedy|Romance 4,Waiting to Exhale (1995),Comedy|Drama|Romance 5,Father of the Bride Part II (1995),Comedy
--autodetectはこの辺も含めて「自動検知」してくれてたんですね。という訳でヘッダ行スキップを指示する--skip_leading_rows (行数)の指定を追加(オプション指定は末尾に追加、では駄目な模様)、再実行。今度は上手く行きました。
$ bq load \ --location=asia-northeast1 \ --source_format=CSV \ --skip_leading_rows 1 \ cmbqdataset.bqtable_from_localfile_def_schema \ /home/ec2-user/ml-25m/movies.csv \ movieId:INTEGER,title:STRING,genres:STRING Upload complete. Waiting on bqjob_rxxxxxxxxxxxxxxxxxxxxxxx_1 ... (0s) Current status: DONE
ただこちらの方法、カラムのNOT NULL指定が行えない模様。この条件を許容出来るのであれば良いですが、そうでない場合は別の手法での取り込みを検討すべきですね。
注: コマンドラインでスキーマを指定する場合は、RECORD（STRUCT）型とフィールド記述を含めることはできず、フィールド モードも指定できません。すべてのモードはデフォルトの NULLABLE になります。フィールドの説明、モード、RECORD 型を含めるには、代わりに JSON スキーマ ファイルを指定します。
JSON データを新しいテーブルに読み込む - Cloud Storage からの JSON データの読み込み | BigQuery | Google Cloud
その他、CSV データの読み込みに関するオプション指定については以下の形式となっています。JSONについてはこちらを参照。
- フィールド区切り文字: カンマ、タブ、パイプ、カスタム(--field_delimiter)
- スキップするヘッダー行(--skip_leading_rows)
- 許可されているエラー数(--max_bad_records)
- 引用符で囲まれた改行を許可する(--allow_quoted_newlines)
- ジャグ行を許可する(--allow_jagged_rows)
- 不明な値を無視する(--ignore_unknown_values)
- 引用符(--quote)
- エンコード(--encoding)
クライアントライブラリ(Python)で実践
ローカルファイルアップロードによるテーブル作成をPythonで実現する場合のサンプルコードは以下。
from google.cloud import bigquery
client = bigquery.Client()
filename = '/home/ec2-user/ml-25m/movies.csv'
dataset_id = 'cmbqdataset'
table_id = 'bqtable_via_python'
dataset_ref = client.dataset(dataset_id)
table_ref = dataset_ref.table(table_id)
job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1
job_config.autodetect = True
with open(filename, "rb") as source_file:
job = client.load_table_from_file(source_file, table_ref, job_config=job_config)
job.result() # Waits for table load to complete.
print("Loaded {} rows into {}:{}.".format(job.output_rows, dataset_id, table_id))
実行結果は以下の通り。
$ python create-bq-table.py Loaded 62423 rows into cmbqdataset:bqtable_via_python.
Pythonライブラリにおける設定周りの詳細については下記APIドキュメントを参照。また、CLIでは出来ていなかったカラムスキーマの詳細設定についてもこちらでは対応可能となっているので、合わせてご参照頂けますと幸いです。
- google.cloud.bigquery.job.LoadJobConfig — google-cloud-bigquery 0.1.0 documentation
- REST Resource: tables | BigQuery | Google Cloud
まとめ
という訳で、BigQueryのテーブル作成をローカルファイルアップロード経由で実践してみた内容のまとめでした。
ローカルファイルに関しても割と簡単な手順で取り込む出来るのは嬉しいところですね。「自動検知」もなかなかに便利な機能です。カラムスキーマの詳細設定が出来るか否かの部分で採用に踏み切るか、悩ましい部分ではありますが上手く見極めて活用していきたいところです。
